const { conn } = require('./mysql')
//console.log(conn)
function queryAll() {
    //执行一个查询
    return new Promise((resolve, reject) => {
        // 执行一个查询
        conn.query('select * from user_tel', function (error, results) {
            if (error) reject(error)
            resolve(results)
        });
    })
}
function queryCount() {
    //执行一个查询
    return new Promise((resolve, reject) => {
        // 执行一个查询
        conn.query('select count(1) from user_tel', function (error, results) {
            if (error) reject(error)
            resolve(results)
        });
    })
}

function queryById(id) {
    // conn.query('select * from user_tel where id=?', [id], function (error, results, fields) {
    //     if (error) throw error;
    //     console.log(results);
    // });
    return new Promise((resolve, reject) => {
        // 执行一个查询
        conn.query('select * from user_tel where id=?', [id], function (error, results) {
            if (error) reject(error)
            resolve(results)
        });
    })
}
// yxl设备购买统计信息
function listPage(offset, rows) {
    //执行一个查询
    return new Promise((resolve, reject) => {
        const sql=`SELECT t.*, 
        COUNT(1) over() countNum,
        COUNT(1) over(PARTITION BY username) buyNum, 
        date_format(MAX(create_time) over(PARTITION BY username),'%Y-%m-%d') buyDate, 
        ROW_NUMBER() over(PARTITION BY username ORDER BY t.create_time) buyOrder
        FROM user_tel t limit ?, ?;`;
        // 执行一个查询
        conn.query(sql, [offset, rows], function (error, results) {
            if (error) reject(error)
            resolve(results)
        });
    })
}
//queryAll();
//queryById(1);
function test() {
    queryById(3);
}
module.exports = {
    queryById,
    queryAll,
    listPage,
    queryCount
}